package com.matt.repository.impl;

import com.matt.domain.Clientele;
import com.matt.domain.Contract;
import com.matt.foundation.constant.ContractConstant;
import com.matt.foundation.query.ContractQuery;
import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.utils.QueryUtil;
import com.matt.foundation.vo.ContractVo;
import com.matt.repository.base.ContractNativeRepository;
import com.matt.repository.base.BaseNativeSqlRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * ********************o
 * Created by Ron
 * from com.matt
 * on 2018/6/19 0019.
 * *********************
 */
@Service
public class ContractNativeRepositoryImpl extends BaseNativeSqlRepository implements ContractNativeRepository {


    @Override
    public List<ContractVo> findByConditon(ContractQuery contractQuery) {
        String querySql = String.format("select co.created as created,co.deleted as deleted, co.id as id,co.name as name,co.code as code,co.beginDate as beginDate,co.endDate as endDate," +
                "co.signDate as signDate,co.payType as payType,co.payTypeVal as payTypeVal,co.payChannel as payChannel,co.payChannelVal as payChannelVal,co.totalAmount as totalAmount,co.practicalAmount as practicalAmount," +
                "co.attachmentUrl as attachmentUrl,co.clienteleId as clienteleId,co.areaId as areaId,co.type as type, co.remark as remark,co.staffId as staffId,co.staffName as staffName, " +
                "co.status as status,co.statusVal as statusVal,cl.name as clienteleName,cl.sysChildName as sysChildName,co.payStatus as payStatus,co.payStatusVal as payStatusVal," +
                "(select group_concat(pr.name separator '|') from contract_product cpr, product as pr WHERE cpr.productId=pr.id and cpr.contractId=co.id) as productName  from contract as co left join clientele as cl on co.clienteleId=cl.id where true");
        //客户合同  contract as co,clientele as cl where co.clienteleId=cl.id
        if (StringUtils.isNotBlank(contractQuery.getClienteleId()))
            querySql +=String.format(" and co.clienteleId='%s'",contractQuery.getClienteleId());

        //1.合同状态 wbm:待回款 0:未开始 1:进行中 3:已结束  2:终止
        if (StringUtils.isNotBlank(contractQuery.getStatusKey()) && "wbm".equals(contractQuery.getStatusKey()))
            querySql += String.format(" and co.payStatus=0");
        if (StringUtils.isNotBlank(contractQuery.getStatus()))
            querySql += String.format(" and co.status IN(%s)",contractQuery.getStatus());

        //2.条件查询  过滤月龄
        if(StringUtils.isNotBlank(contractQuery.getQueryKey()) && StringUtils.isNotBlank(contractQuery.getQueryVal()) && !"monthAge".equals(contractQuery.getQueryKey())){
            querySql +=String.format(" and co.clienteleId IN(select id from clientele where "+contractQuery.getQueryKey()+" like concat('%s','%s','%s') ", "%", QueryUtil.replaceAllSql(contractQuery.getQueryVal()),"%");
            querySql +=String.format(" )");
        }

        //3.孩子月龄
        if (StringUtils.isNotBlank(contractQuery.getQueryKey()) && "monthAge".equals(contractQuery.getQueryKey())   && StringUtils.isNotBlank(contractQuery.getQueryVal())){
            querySql +=String.format(" and co.clienteleId IN( select DISTINCT(cli.id) from clientele as cli,contract as co where cli.id=co.clienteleId and ((YEAR(NOW()) - YEAR(cli.birthday)) * 12 + (MONTH(NOW()) - MONTH(cli.birthday)))=%s",contractQuery.getQueryVal());
            querySql +=String.format(" )");
        }
        //创建时间开始
        if(contractQuery.getBeginDate() != null){
            querySql += String.format(" and co.created >= '%s'", contractQuery.getBeginDate());
        }

        //创建时间结束
        if(contractQuery.getEndDate() != null){
            querySql += String.format(" and co.created < '%s'", contractQuery.getEndDate());
        }


        //我的合同
       if (StringUtils.isNotBlank(contractQuery.getStaffId()))
           querySql +=String.format(" and co.staffId='%s'",contractQuery.getStaffId());

        querySql +=String.format(" and co.areaId='%s'",contractQuery.getAreaId());
        querySql += " order by co.created desc " ;
        querySql += String.format(" limit %s, %s ", contractQuery.getPageNum() * contractQuery.getPageSize() ,  contractQuery.getPageSize());
        List<ContractVo> objectArrayList = sqlObjectList(querySql, ContractVo.class);
        return objectArrayList;
    }

    @Override
    public Long countContract(ContractQuery contractQuery) {
        String querySql = String.format("select count(co.id) from contract as co where true");
        //客户合同
        if (StringUtils.isNotBlank(contractQuery.getClienteleId()))
            querySql +=String.format(" and co.clienteleId='%s'",contractQuery.getClienteleId());

        //1.合同状态 wbm:待回款 0:未开始 1:进行中 3:已结束  2:终止
        if (StringUtils.isNotBlank(contractQuery.getStatusKey()) && "wbm".equals(contractQuery.getStatusKey()))
            querySql += String.format(" and co.payStatus=0");
        if (StringUtils.isNotBlank(contractQuery.getStatus()))
            querySql += String.format(" and co.status IN(%s)",contractQuery.getStatus());


        //2.条件查询 过滤月龄
        if(StringUtils.isNotBlank(contractQuery.getQueryKey()) && StringUtils.isNotBlank(contractQuery.getQueryVal()) && !"monthAge".equals(contractQuery.getQueryKey())){
            querySql +=String.format(" and co.clienteleId IN(select id from clientele where "+contractQuery.getQueryKey()+" like concat('%s','%s','%s') ", "%",QueryUtil.replaceAllSql(contractQuery.getQueryVal()),"%");
            querySql +=String.format(" )");
        }

        //3.孩子月龄
        if (StringUtils.isNotBlank(contractQuery.getQueryKey()) && "monthAge".equals(contractQuery.getQueryKey())   && StringUtils.isNotBlank(contractQuery.getQueryVal())){
            querySql +=String.format(" and co.clienteleId IN( select DISTINCT(cli.id) from clientele as cli,contract as co where cli.id=co.clienteleId and ((YEAR(NOW()) - YEAR(cli.birthday)) * 12 + (MONTH(NOW()) - MONTH(cli.birthday)))=%s",contractQuery.getQueryVal());
            querySql +=String.format(" )");
        }
        //创建时间开始
        if(contractQuery.getBeginDate() != null){
            querySql += String.format(" and co.created >= '%s'", contractQuery.getBeginDate());
        }

        //创建时间结束
        if(contractQuery.getEndDate() != null){
            querySql += String.format(" and co.created < '%s'", contractQuery.getEndDate());
        }
        //我的合同
        if (StringUtils.isNotBlank(contractQuery.getStaffId()))
            querySql +=String.format(" and co.staffId='%s'",contractQuery.getStaffId());

        querySql +=String.format(" and co.areaId='%s'",contractQuery.getAreaId());
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public ContractVo findById(String contractId) {
        String querySql = String.format("select co.payStatus as payStatus,co.payStatusVal as payStatusVal, co.created as created,co.deleted as deleted, co.id as id,co.name as name,co.code as code,co.beginDate as beginDate,co.endDate as endDate," +
                "co.signDate as signDate,co.payType as payType,co.payTypeVal as payTypeVal,co.payChannel as payChannel,co.payChannelVal as payChannelVal,co.totalAmount as totalAmount,co.practicalAmount as practicalAmount," +
                "co.attachmentUrl as attachmentUrl,co.clienteleId as clienteleId,co.areaId as areaId,co.type as type,co.remark as remark,co.staffId as staffId,co.staffName as staffName, co.breakDownReason as breakDownReason,co.breakDownRemark as breakDownRemark," +
                "co.status as status,co.statusVal as statusVal,cl.name as clienteleName,IFNULL(cl.sysChildName,cl.name) as sysChildName from contract as co left join clientele as cl on co.clienteleId=cl.id where true");
       if (StringUtils.isNotBlank(contractId))
           querySql +=String.format(" and co.id='%s'",contractId);

        List<ContractVo> list=sqlObjectList(querySql,ContractVo.class);
       return list.size()>0 ? list.get(0):null;
    }

    @Override
    public List<Clientele> findContractBySyschildName(String areaId,String sysChildName) {
        String querySql = String.format("SELECT cl.id,cl.channel,cl.fromUser,cl.category,cl.status,cl.sysChildName,cl.childName,cl.birthday," +
                "cl.nickName,cl.englishName,cl.gender,cl.bloodType,cl.nationality,cl.targetGarden,cl.registerResidence,cl.registerType," +
                "cl.languageEnvironment,cl.name,cl.clienteleType,cl.relationship,cl.mobile,cl.homeAddress,cl.company,cl.duty,cl.degree,cl.signStatus as signStatus," +
                "cl.staffId,cl.lastModified,cl.lastModifiedId,cl.areaId,cl.lastFollowTime,cl.lastFollowRecord,cl.adviceName from clientele as cl where true");
        if (StringUtils.isNotBlank(sysChildName))
            querySql +=String.format(" and  cl.sysChildName like concat('%s','%s','%s') ", "%",sysChildName,"%");

        querySql +=String.format(" and cl.areaId='%s'",areaId);
        querySql +=String.format( " order by created desc");
        List<Clientele> list=sqlObjectList(querySql,Clientele.class);
        return list;
    }

    @Override
    public List<ContractVo> findByClienteleId(String clienteleId) {
        String querySql = String.format("select co.created as created,co.deleted as deleted, co.id as id,co.name as name,co.code as code,co.beginDate as beginDate,co.endDate as endDate,co.payStatus as payStatus,co.payStatusVal as payStatusVal," +
                "co.signDate as signDate,co.payType as payType,co.payTypeVal as payTypeVal,co.payChannel as payChannel,co.payChannelVal as payChannelVal,co.totalAmount as totalAmount,co.practicalAmount as practicalAmount," +
                "co.attachmentUrl as attachmentUrl,co.clienteleId as clienteleId,co.areaId as areaId,co.type as type,co.remark as remark,co.staffId as staffId,co.staffName as staffName, " +
                "co.status as status,co.statusVal as statusVal from contract as co where co.clienteleId='%s'",clienteleId);
        querySql += String.format(" order by co.created desc");
        List<ContractVo> list = sqlObjectList(querySql,ContractVo.class);
        return list;
    }

    @Override
    public Long findCountByClienteleId(String clienteleId) {
        String querySql = String.format("select count(id) from contract where clienteleId='%s'",clienteleId);
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long findByStatusAndPayStatus(String clienteleId, int status, int payStatus) {
        String querySql = String.format("select count(id) from contract where clienteleId='%s' and status <> '%s' and payStatus='%s'",clienteleId,status,payStatus);
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long signAllContractNum(String staffId, Date begin, Date end) {
        String querySql = String.format("select count(id) from contract where staffId='%s' and date(created) >=date('%s') and date(created) <=date('%s') and status !='%s' ",staffId, DateUtil.dateToymd(begin),DateUtil.dateToymd(end),ContractConstant.Status.STOP.codeOf());
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long signFormalContractNum(String staffId, Date begin, Date end) {
        String querySql = String.format("select count(id) from contract where staffId='%s' and date(created) >= date('%s') and date(created) <= date('%s') and TO_DAYS(endDate) - TO_DAYS(beginDate) >= 30 and status !='%s' ",staffId,DateUtil.dateToymd(begin),DateUtil.dateToymd(end), ContractConstant.Status.STOP.codeOf());
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long signEffectiveContractNum(String staffId, Date begin, Date end) {
        String querySql = String.format("select count(id) from contract where staffId='%s' and year(created)=year('%s') and MONTH(created)=MONTH('%s') and status !='%s' and TO_DAYS(endDate) - TO_DAYS(beginDate) >= 30 and date('%s') >= date(beginDate) and date('%s') <= date(endDate)",staffId,DateUtil.dateToymd(begin),DateUtil.dateToymd(begin), ContractConstant.Status.STOP.codeOf(),DateUtil.dateToymd(begin),DateUtil.dateToymd(end));
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long areaContractNum(String areaId, Date begin, Date end) {
        String querySql = String.format("select count(id) from contract where areaId='%s' and date(beginDate) >= date('%s') and date(beginDate) <= date('%s') and TO_DAYS(endDate) - TO_DAYS(beginDate) >= 30 and status !='%s' ",areaId,DateUtil.dateToymd(begin),DateUtil.dateToymd(end), ContractConstant.Status.STOP.codeOf());
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long proceedContractNum(String staffId,Date begin,Date end) {
        String querySql = String.format("select count(id) from contract where staffId='%s' and date(endDate) >= date('%s') and date(beginDate) <= date('%s') and status !='%s' ",staffId,DateUtil.dateToymd(begin),DateUtil.dateToymd(end), ContractConstant.Status.STOP.codeOf());
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<String> findByStaffIdAndStatus(String staffId, int status) {
        String querySql = String.format("select DISTINCT(clienteleId) from contract where staffId='%s' and status='%s'",staffId,status);
        return sqlSingleList(querySql);
    }

	@Override
	public List<ContractVo> checkContractCode(String areaId,String code) {
		String querySql = String.format("select co.code as code from contract as co where areaId = '%s' ",areaId);
		querySql+= String.format(" AND `code` = '%s'",code);
		return sqlObjectList(querySql,ContractVo.class);
	}

	@Override
	public List<ContractVo> findListByClienteleId(String areaId, String clienteleId) {
		String querySql = "SELECT c.id,c.practicalAmount,c.totalAmount,c.beginDate,c.endDate,c.type "
				+ " FROM contract c "
				+ " WHERE   c.deleted = 0 AND (c.`status` = 0 OR c.`status` = 1)  "
				+ " AND endDate >= NOW()";

        querySql+=	String.format(" AND c.areaId = '%s'", areaId);
		querySql+=	String.format(" AND c.clienteleId = '%s'", clienteleId);
		return sqlObjectList(querySql,ContractVo.class);
	}


    @Override
    public List<ContractVo> findListByClienteleIdDesc(String areaId, String clienteleId) {
        String querySql = "SELECT c.id,c.practicalAmount,c.totalAmount,c.beginDate,c.endDate,c.type "
                + " FROM contract c "
                + " WHERE   c.deleted = 0   ";

        querySql+=	String.format(" AND c.areaId = '%s'", areaId);
        querySql+=	String.format(" AND c.clienteleId = '%s'", clienteleId);
        querySql+= " ORDER BY endDate DESC LIMIT 1" ;
        List objectArrayList = sqlObjectList(querySql,ContractVo.class);
        return objectArrayList;
    }


    @Override
    public List<Contract> findByClienteleIdList(String areaId, String clienteleId, String beginDate,String endDate) {
        String querySql = "SELECT c.id,c.practicalAmount,c.totalAmount,c.beginDate,c.endDate,c.type,c.clienteleId,c.surplusLeaveDay,c.code,c.name   "
                + " FROM contract c  "
                + " WHERE   c.deleted = 0  and c.payStatus = 1  ";

        querySql+=	String.format(" AND c.areaId = '%s'", areaId);
        querySql+=	String.format(" AND c.type = '%s'", 1);
        if (Objects.nonNull(clienteleId)) {
            querySql += String.format(" AND c.clienteleId = '%s'", clienteleId);
        }

        if (Objects.nonNull(endDate) && Objects.nonNull(beginDate) &&
                !StringUtils.equals(endDate,"null") && !StringUtils.equals(beginDate,"null")) {
            querySql+=	String.format("  and date(c.endDate) >=  date('%s') ", endDate);
            querySql+=	String.format("  and date(c.beginDate) <=date('%s') ", beginDate);
        }

        querySql+= " ORDER BY c.endDate DESC " ;
        List objectArrayList = sqlObjectList(querySql, Contract.class);
        return objectArrayList;
    }


    public List<Contract> findByAreaIdList(String areaId,   String clienteleId,String beginDate,String endDate) {
        String querySql = "SELECT c.id, sum(c.practicalAmount) practicalAmount,sum(c.totalAmount) totalAmount ,c.beginDate,c.endDate,c.type,c.clienteleId,c.surplusLeaveDay,SUM(TIMESTAMPDIFF(day,beginDate,endDate) ) contractDay ,IFNULL(sum(cp.practicalAmount) ,0)  monthPrice  "
                + " FROM contract c  LEFT JOIN contract_product cp on cp.contractId = c.id   "
                + " WHERE   c.deleted = 0  and c.payStatus = 1  ";

        querySql+=	String.format(" AND  c.areaId = '%s'", areaId);
        querySql+=	String.format(" AND c.type = '%s'", 1);

        if (Objects.nonNull(clienteleId)) {
            querySql += String.format(" AND c.clienteleId = '%s'", clienteleId);
        }

        if (Objects.nonNull(endDate) && Objects.nonNull(beginDate) &&
                !StringUtils.equals(endDate,"null") && !StringUtils.equals(beginDate,"null")) {
            querySql+=	String.format("  and date(c.endDate) >=  date('%s') ", endDate);
            querySql+=	String.format("  and date(c.beginDate) <= date('%s') ", beginDate);
        }

        querySql+= "GROUP BY c.clienteleId   ORDER BY c.endDate DESC " ;
        List objectArrayList = sqlObjectList(querySql, Contract.class);
        return objectArrayList;
    }
}
